﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Automobile.service.Common;
using Automobile.service.Model;
using System.Data;
using System.Data.SqlClient;

namespace Automobile.service.DAL
{
    
    public class StockDAL
    {
        SQLHelper h = new SQLHelper();
        /// <summary>
        /// 库存的分页显示
        /// </summary>
        /// <param name="PageIndex"></param>
        /// <param name="PageSize"></param>
        /// <returns></returns>
        public List<Stock> ShowStock(int PageIndex,int PageSize)
        {
            string sql = "select Stock_Id,Stock_num,Stock_warn,WareHose_Name,Product_Name from (select ROW_NUMBER() over(order by Stock_Id) as nid,Stock_Id,Stock_num,Stock_warn,WareHose_Name,Product_Name from Stock join WareHose on Stock.WareHose_Id=WareHose.WareHose_Id join Product on Stock.Product_Id=Product.Product_Id) as temp where nid between (" + PageIndex + "-1)*" + PageSize + "+1 and " + PageIndex + "*" + PageSize + "";
            List<Stock> list = new List<Stock>();
            DataTable data = h.ExecDataTable(sql);
            foreach (DataRow item in data.Rows)
            {
                Stock s = new Stock();
                s.Stock_Id = Convert.ToInt32(item["Stock_Id"]);
                s.Stock_num = Convert.ToInt32(item["Stock_num"]);
                s.Stock_warn = Convert.ToInt32(item["Stock_warn"]);
                s.WareHose_Name = item["WareHose_Name"].ToString();
                s.Product_Name = item["Product_Name"].ToString();
                list.Add(s);
            }
            return list;
        }
        public int Count()
        {
            string sql = "select count(*) from Stock";
            return Convert.ToInt32( h.ExecScalar(sql));
        }
        /// <summary>
        /// 获取单行数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataTable GetStockById(int id)
        {
            string sql = "select Stock_Id,WareHose_Name,Product_Name,Stock_num,Stock.WareHose_Id,Stock.Product_Id from Stock join WareHose on Stock.WareHose_Id=WareHose.WareHose_Id join Product on Stock.Product_Id=Product.Product_Id where Stock_Id=" + id + "";
            return h.ExecDataTable(sql);
        }
        public List<Blitem_Type> GetPandianType()
        {
            string sql = "select * from Blitem_Type";
            List<Blitem_Type> list = new List<Blitem_Type>();
            DataTable data = h.ExecDataTable(sql);
            foreach (DataRow item in data.Rows)
            {
                Blitem_Type bt = new Blitem_Type();
                bt.Blitem_Type_Id = Convert.ToInt32(item["Blitem_Type_Id"]);
                bt.Blitem_Type_name = item["Blitem_Type_name"].ToString();
                list.Add(bt);
            }
            return list;
        }
        public int AddPandian(Blitem b)
        {
            string procname = "Pandian";
            SqlParameter[] par = new SqlParameter[]{
                new SqlParameter("@Stock_num",b.Stock_Num),
                new SqlParameter("@Blitem_Etc",b.Blitem_Etc),
                new SqlParameter("@WareHose_Id",b.WareHose_Id),
                new SqlParameter("@Blitem_date",b.Blitem_date),
                new SqlParameter("@Blitem_num",b.Blitem_num),
                new SqlParameter("@Blitem_Rownum",b.Blitem_Rownum),
                new SqlParameter("@Blitem_Stock_num",b.Blitem_Stock_num),
                new SqlParameter("@Product_Id",b.Product_Id),
                new SqlParameter("@Blitem_Type_Id",b.Blitem_Type_Id)
            };
            return h.ExecNonquery_Proc(procname, par);
        }
         //<summary>
         //根据商品名或仓库名查询库存信息
         //</summary>
         //<param name="PageIndex"></param>
         //<param name="PageSize"></param>
         //<returns></returns>
        public List<Stock> ShowStock_kucun(int PageIndex, int PageSize,string Product_Name)
        {
            string sql = "select Stock_Id,Stock_num,Stock_warn,WareHose_Name,Product_Name from (select ROW_NUMBER() over(order by Stock_Id) as nid,Stock_Id,Stock_num,Stock_warn,WareHose_Name,Product_Name from Stock join WareHose on Stock.WareHose_Id=WareHose.WareHose_Id join Product on Stock.Product_Id=Product.Product_Id) as temp where nid between (" + PageIndex + "-1)*" + PageSize + "+1 and " + PageIndex + "*" + PageSize + "";
            if(!string.IsNullOrEmpty(Product_Name))
            {
                sql+="and Product_Name like '%"+Product_Name+"%'";
            }
            List<Stock> list = new List<Stock>();
            DataTable data = h.ExecDataTable(sql);
            foreach (DataRow item in data.Rows)
            {
                Stock s = new Stock();
                s.Stock_Id = Convert.ToInt32(item["Stock_Id"]);
                s.Stock_num = Convert.ToInt32(item["Stock_num"]);
                s.Stock_warn = Convert.ToInt32(item["Stock_warn"]);
                s.WareHose_Name = item["WareHose_Name"].ToString();
                s.Product_Name = item["Product_Name"].ToString();
                list.Add(s);
            }
            return list;
        }
    }
}
